I recently received a request to deliver a report that would allow end users to choose to see data filtered by a specific year or month, or as a year-to-date (YTD) calculation. 

The first thing that came to my mind was to play around with buttons, actions and bookmarks, but I decided to apply a different approach to tackle this using dynamic filtering in Microsoft Power BI. 

I’ll use the Contoso database for this example.

The first step is to create a measure for sales amount:

I also need a measure that will calculate year-to-date sales amount (YTD), which we will create it as:

We are simply using the built-in data analysis expression (DAX) function DATESYTD, which will automatically evaluate an expression and return the desired values. That way, when I drag a table to the Power BI desktop canvas and select the year 2009, I’m going to get the following numbers for our newly created measures:

The “Sales Amt” column shows figures for every selected month, while “Sales Amt YTD” adds those figures to display YTD values.

Check Out These BI Tools 22 Business Intelligence Tools to Know

Now, I need to find a way to enable users to see one of the specific values based on their selection. While this can be achieved using buttons and bookmarks and hiding and showing visuals based on the user’s selection, let’s try a slightly different approach.

First, we’ll create a new table, which will hold data for our dynamic filter:

Under “Enter Data,” I’ve just created a simple table called “Calculation TimeFrame” with two columns: “ID” and “TimeFrame.” Of course, you can define as many options as you want depending on your needs. After I load this table to a model, I need to find a way to somehow connect it with my existing model.

Next, I need to know what the user selected to display. Therefore, the following measure needs to be created:

This measure will return the minimum ID value of user selection. In case no value is selected, the option with ID = 1 (Monthly) will be displayed.

The next step is the most interesting since it puts the user’s selection into the context of the existing data model. Within the online sales table, I’ll create the following measure:

This measure takes the ID value from user selection, and based on that value, displays the respective calculation. It can easily be done with the SWITCH function. With this function, if the user chooses ID 1 (Monthly), it’ll return the “Sales Amt” value. If he chooses ID 2 (YTD), it’ll return the YTD value. Simple as that.

More Data Visualization Using T-SNE in Python to Visualize High-Dimensional Data Sets

The final touch is to create a filter for this. Simply drag the slicer visual and enter the time frame field. Make that horizontal, so it looks a bit nicer:

As you can see, the default monthly time frame will be displayed:

However, if you click on the YTD slicer button, the visuals will perform differently:

Now, visuals show YTD values.

This is a neat trick to avoid using buttons and bookmarks for your users’ requests while still keeping everything tidy. It’s important to define proper measures and to link your custom tables with the data model. After that, it’s up to you to define the limits of usage for this technique.

Built In’s expert contributor network publishes thoughtful, solutions-oriented stories written by innovative tech professionals. It is the tech industry’s definitive destination for sharing compelling, first-person accounts of problem-solving on the road to innovation.